﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agresso.Interface.Layer.Gateway;
using Agresso.Interface.Layer.Domain;
using System.Data.SqlClient;
using Agresso.Interface.Layer.SQL;
using System.Data;
using System.Collections;

namespace Agresso.Interface.Layer.Service
{
    public class ProfitCentreService
    {
        DataBaseConnectivity m_dataBaseConectivity;
        AlertService m_alertService;
        agldimvalueService m_agldimvalueService;

        public ProfitCentreService(DataBaseConnectivity dataBaseConectivity)
        {
            m_dataBaseConectivity = dataBaseConectivity;
            m_alertService = new AlertService();
            m_agldimvalueService = new agldimvalueService();
        }
        public ProfitCentreService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
            m_alertService = new AlertService();
            m_agldimvalueService = new agldimvalueService();
        }
        public IList GetAllProfitCentreMapping()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(ProfitCentreSQL.GetAllProfitCentreMapping(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        result.Add(new ProfitCentre(false, rd));
                    }
                }
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public bool IsValidProfitCentreMapping(string profitCentreName)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                //m_dataBaseConectivity.AgressoSqlConnection.Open();
                //find pc in pc mapping table-------------
                SqlCommand cmd = new SqlCommand(ProfitCentreSQL.GetProfitCentreMappingByName(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@full_name", SqlDbType.NVarChar).Value = profitCentreName;
                SqlDataReader rd = cmd.ExecuteReader();
                ProfitCentre profitcentre = null;
                if (rd.HasRows)
                {
                    rd.Read();
                    profitcentre = new ProfitCentre(false, rd);
                }
                rd.Close();
                //if pc did not found in Mapping Table then get from softmar pc master and store in pc mapping table-----------
                if (profitcentre == null)
                {
                    cmd.CommandText = ProfitCentreSQL.GetSoftmarProfitCentreByName();
                    cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@full_name", SqlDbType.NVarChar).Value = profitCentreName;
                    rd = cmd.ExecuteReader();
                    if (rd.HasRows)
                    {
                        rd.Read();
                        profitcentre = new ProfitCentre(true, rd);
                    }
                    else
                    {
                        throw new Exception("CAn not Find Profit CEntre in Softmar Profit CEntre : " + profitCentreName);
                    }
                    rd.Close();
                    //if vessel found in Softmar Vessel master then store in mapping table------------
                    if (profitcentre != null)
                    {
                        //Insert vessel to vessel mapping table---------
                        cmd.CommandText = ProfitCentreSQL.InsertProfitCentreMapping();
                        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@account_code", SqlDbType.NVarChar).Value = profitcentre.account_code;
                        cmd.Parameters.Add("@active", SqlDbType.NVarChar).Value = profitcentre.active;
                        cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = profitcentre.city;
                        cmd.Parameters.Add("@co_type", SqlDbType.NVarChar).Value = profitcentre.co_type;
                        cmd.Parameters.Add("@country", SqlDbType.NVarChar).Value = profitcentre.country;
                        cmd.Parameters.Add("@created_date", SqlDbType.NVarChar).Value = DateTime.Now;
                        cmd.Parameters.Add("@full_address", SqlDbType.NVarChar).Value = profitcentre.full_address;
                        cmd.Parameters.Add("@full_name", SqlDbType.NVarChar).Value = profitcentre.full_name;
                        cmd.Parameters.Add("@modified_date", SqlDbType.NVarChar).Value = DateTime.Now;
                        cmd.Parameters.Add("@short_code", SqlDbType.NVarChar).Value = profitcentre.short_code;
                        cmd.Parameters.Add("@short_name", SqlDbType.NVarChar).Value = profitcentre.short_name;
                        cmd.ExecuteNonQuery();

                        //get vessel from agresso-------------
                        //cmd.CommandText = AgressoSQL.GetAglDimValueVesselByDescription();
                        //cmd.Connection = m_dataBaseConectivity.AgressoSqlConnection;
                        //cmd.Parameters.Clear();
                        //cmd.Parameters.Add("@description", SqlDbType.NVarChar).Value = vessel.vessel_name;
                        //rd = cmd.ExecuteReader();
                        //if (rd.HasRows)
                        //{
                        //    rd.Read();
                        //    agldimvalue agldim = new agldimvalue(rd);
                        //    vessel.agr_description = agldim.description;
                        //    vessel.agr_dim_value = agldim.dim_value;
                        //    vessel.agr_rel_value = agldim.rel_value;
                        //}
                        //rd.Close();
                    }
                }
                return profitcentre == null ? false : profitcentre.agr_description != string.Empty;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
            }
            return false;
        }
        public ProfitCentre GetProfitCentreMapping(string profitCentreName)
        {
            ProfitCentre profitcentre = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                //find pc in pc mapping table-------------
                SqlCommand cmd = new SqlCommand(ProfitCentreSQL.GetProfitCentreMappingByName(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@full_name", SqlDbType.NVarChar).Value = profitCentreName;
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    rd.Read();
                    profitcentre = new ProfitCentre(false, rd);
                    rd.Close();
                }
                else
                {
                    rd.Close();
                    //get from short name------------------
                    cmd = new SqlCommand(ProfitCentreSQL.GetProfitCentreMappingByShortName(), m_dataBaseConectivity.InterfaceSqlConnection);
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@short_name", SqlDbType.NVarChar).Value = profitCentreName;
                    rd = cmd.ExecuteReader();
                    if (rd.HasRows)
                    {
                        rd.Read();
                        profitcentre = new ProfitCentre(false, rd);
                    }
                    rd.Close();
                }
                //if pc did not found in Mapping Table then get from softmar pc master and store in pc mapping table-----------
                if (profitcentre == null)
                {
                    cmd.CommandText = ProfitCentreSQL.GetSoftmarProfitCentreByName();
                    cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@full_name", SqlDbType.NVarChar).Value = profitCentreName;
                    rd = cmd.ExecuteReader();
                    if (rd.HasRows)
                    {
                        rd.Read();
                        profitcentre = new ProfitCentre(true, rd); rd.Close();
                    }
                    else
                    {
                        rd.Close();
                        cmd.CommandText = ProfitCentreSQL.GetSoftmarProfitCentreByShortName();
                        cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@full_name", SqlDbType.NVarChar).Value = profitCentreName;
                        rd = cmd.ExecuteReader();
                        if (rd.HasRows)
                        {
                            rd.Read();
                            profitcentre = new ProfitCentre(true, rd);
                            rd.Close();
                        }
                        else
                        {
                            //throw new Exception("CAn not Find Profit CEntre in Softmar Profit CEntre : " + profitCentreName);
                            profitcentre = new ProfitCentre();
                            profitcentre.full_name = profitCentreName;
                            profitcentre.short_code = "Can not found in Profit Centre Softmar Master";
                            rd.Close();
                        }
                    }
                    
                    //if vessel found in Softmar Vessel master then store in mapping table------------
                    if (profitcentre != null)
                    {
                        //Insert vessel to vessel mapping table---------
                        cmd.CommandText = ProfitCentreSQL.InsertProfitCentreMapping();
                        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@account_code", SqlDbType.NVarChar).Value = profitcentre.account_code;
                        cmd.Parameters.Add("@active", SqlDbType.NVarChar).Value = profitcentre.active;
                        cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = profitcentre.city;
                        cmd.Parameters.Add("@co_type", SqlDbType.NVarChar).Value = profitcentre.co_type;
                        cmd.Parameters.Add("@country", SqlDbType.NVarChar).Value = profitcentre.country;
                        cmd.Parameters.Add("@created_date", SqlDbType.NVarChar).Value = DateTime.Now;
                        cmd.Parameters.Add("@full_address", SqlDbType.NVarChar).Value = profitcentre.full_address;
                        cmd.Parameters.Add("@full_name", SqlDbType.NVarChar).Value = profitcentre.full_name;
                        cmd.Parameters.Add("@modified_date", SqlDbType.NVarChar).Value = DateTime.Now;
                        cmd.Parameters.Add("@short_code", SqlDbType.NVarChar).Value = profitcentre.short_code;
                        cmd.Parameters.Add("@short_name", SqlDbType.NVarChar).Value = profitcentre.short_name;
                        cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = profitcentre.client;
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = ProfitCentreSQL.MaxProfitCentreID();
                        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                        cmd.Parameters.Clear();
                        profitcentre.profit_centre_id = Convert.ToInt32(cmd.ExecuteScalar());
                    }
                }
                // search base on softmar Profit centre name---
                if (profitcentre.agr_dim_value == "")
                {
                    agldimvalue agrProfitCentre = m_agldimvalueService.GetAglDimValueProfitCentreByDescription(profitcentre.full_name.Trim());
                    if (agrProfitCentre != null)
                    {
                        profitcentre.agr_description = agrProfitCentre.description;
                        profitcentre.agr_dim_value = agrProfitCentre.dim_value;
                        profitcentre.agr_rel_value = agrProfitCentre.rel_value;
                        profitcentre.client = agrProfitCentre.client;
                        cmd.CommandText = ProfitCentreSQL.UpdateProfitCentreAgressoMapping();
                        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@agr_description", SqlDbType.NVarChar).Value = profitcentre.agr_description;
                        cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = profitcentre.agr_dim_value;
                        cmd.Parameters.Add("@agr_rel_value", SqlDbType.NVarChar).Value = profitcentre.agr_rel_value;
                        cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = profitcentre.client;
                        cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.Parameters.Add("@profit_centre_id", SqlDbType.Int).Value = profitcentre.profit_centre_id;
                        cmd.ExecuteNonQuery();
                    }
                    //else
                    //{   // search base on user description manual entry 
                    //    if (profitcentre.agr_description != string.Empty)
                    //    {
                    //        agrProfitCentre = m_agldimvalueService.GetAglDimValueProfitCentreByDescription(profitcentre.agr_description);
                    //        if (agrProfitCentre != null)
                    //        {
                    //            profitcentre.agr_description = agrProfitCentre.description;
                    //            profitcentre.agr_dim_value = agrProfitCentre.dim_value;
                    //            profitcentre.agr_rel_value = agrProfitCentre.rel_value;
                    //            profitcentre.client = agrProfitCentre.client;
                    //            cmd.CommandText = ProfitCentreSQL.UpdateProfitCentreAgressoMapping();
                    //            cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                    //            cmd.Parameters.Clear();
                    //            cmd.Parameters.Add("@agr_description", SqlDbType.NVarChar).Value = profitcentre.agr_description;
                    //            cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = profitcentre.agr_dim_value;
                    //            cmd.Parameters.Add("@agr_rel_value", SqlDbType.NVarChar).Value = profitcentre.agr_rel_value;
                    //            cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = profitcentre.client;
                    //            cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                    //            cmd.Parameters.Add("@profit_centre_id", SqlDbType.Int).Value = profitcentre.profit_centre_id;
                    //            cmd.ExecuteNonQuery();
                    //        }
                    //    }
                    //}
                }
                return profitcentre;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
            }
            return profitcentre;
        }
        public bool AlertIfNotValidProfitCentre(ProfitCentre profitcentre, string key, AlertType alertType)
        {
            bool valid = profitcentre.IsValidProfitCentre();
            if (valid)
                m_alertService.CheckAndUpdateAlert(key, alertType, AlertStatus.RESOLVED, "Please update Agresso map for Proft Centre with name : " + profitcentre.full_name, "Profit Centre");
            else
                m_alertService.CheckAndUpdateAlert(key, alertType, AlertStatus.PENDING, "Please update Agresso map for Proft Centre with name : " + profitcentre.full_name, "Profit Centre");
            return valid;
        }
        public void UpdateProfitCentreMapping(ProfitCentre profitcentre)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = ProfitCentreSQL.UpdateProfitCentreMapping();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@profit_centre_id", SqlDbType.Int).Value = profitcentre.profit_centre_id;

                cmd.Parameters.Add("@account_code", SqlDbType.NVarChar).Value = profitcentre.account_code;
                cmd.Parameters.Add("@active", SqlDbType.NVarChar).Value = profitcentre.active;
                cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = profitcentre.city;
                cmd.Parameters.Add("@co_type", SqlDbType.NVarChar).Value = profitcentre.co_type;
                cmd.Parameters.Add("@country", SqlDbType.NVarChar).Value = profitcentre.country;
                cmd.Parameters.Add("@full_address", SqlDbType.NVarChar).Value = profitcentre.full_address;
                cmd.Parameters.Add("@full_name", SqlDbType.NVarChar).Value = profitcentre.full_name;
                cmd.Parameters.Add("@short_code", SqlDbType.NVarChar).Value = profitcentre.short_code;
                cmd.Parameters.Add("@short_name", SqlDbType.NVarChar).Value = profitcentre.short_name;

                cmd.Parameters.Add("@agr_description", SqlDbType.NVarChar).Value = profitcentre.agr_description;
                cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = profitcentre.agr_dim_value;
                cmd.Parameters.Add("@agr_rel_value", SqlDbType.NVarChar).Value = profitcentre.agr_rel_value;
                cmd.Parameters.Add("@modified_date", SqlDbType.NVarChar).Value = DateTime.Now;
                cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = profitcentre.client;
                cmd.Parameters.Add("@ccc_dim_value", SqlDbType.NVarChar).Value = profitcentre.ccc_dim_value;
                cmd.Parameters.Add("@expense_account", SqlDbType.NVarChar).Value = profitcentre.expense_account;
                cmd.Parameters.Add("@legal_entity_id", SqlDbType.Int).Value = profitcentre.CommissionManager.profit_centre_id;

                cmd.ExecuteNonQuery();

            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public IList GetAllProfitCentreMapping(string p)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(ProfitCentreSQL.GetAllProfitCentreMapping(p), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        result.Add(new ProfitCentre(false, rd));
                    }
                }
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public ProfitCentre GetProfitCentreByDimValue(string p)
        {
            ProfitCentre result = new ProfitCentre(0);
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(ProfitCentreSQL.GetAllProfitCentreMappingByDimValue(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = p;
                SqlDataReader rd = cmd.ExecuteReader();

                    while (rd.Read())
                    {
                        result = new ProfitCentre(false, rd);
                    }
                
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public ProfitCentre GetProfitCentreByFullName(string full_name)
        {
            ProfitCentre result = new ProfitCentre(0);
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(ProfitCentreSQL.GetProfitCentreByFullName(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@full_name", SqlDbType.NVarChar).Value = full_name;
                SqlDataReader rd = cmd.ExecuteReader();

                while (rd.Read())
                {
                    result = new ProfitCentre(false, rd);
                }

                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public ProfitCentre GetProfitCentreByID(long p)
        {
            ProfitCentre result = new ProfitCentre(0);
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(ProfitCentreSQL.GetAllProfitCentreMappingByID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@profit_centre_id", SqlDbType.Int).Value = p;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new ProfitCentre(false, rd);
                }
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
    }
}
